package gen.tools.query;

import gen.tools.Widgets;
import gen.tools.structures.DepParis;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryDepRecordParis extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public QueryDepRecordParis() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		
		final ArrayList<DepParis> dps = new ArrayList<DepParis>();
		
		try {
			conn = wid.getConn();
			String sql = "SELECT t1.depmainSN,t1.depFN,t5.localVer,t5.fileSizeInByte,t1.eDT,t1.hostIP,t4.empName,t2.fDepActTypeDesc,t1.depResult,t7.deliverySN FROM deltask t7,fdepacttype t2,emps t4,(fdepdetail t1,depmain t3) LEFT JOIN depldetail t5 ON (t5.deplmainSN=t3.deplmainSN AND t5.localFile=t1.depFN) WHERE t7.deplmainSN=t3.deplmainSN AND t3.creator=t4.empId AND t1.depmainSN=t3.depmainSN AND t1.fDepActTypeId=t2.fDepActTypeId AND t2.fDepActTypeId IN(3,5,6,7) ORDER BY t1.depmainSN DESC,t1.fdepdetailid";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			sql="SELECT COUNT(*) FROM fdepdetail WHERE depmainSN=? AND fDepActTypeId IN(3,5,6,7)";
			ps = conn.prepareStatement(sql);
			while ( rs.next() ) {
				final DepParis dp = new DepParis();
				dp.setActTypeDesc( rs.getString("fDepActTypeDesc") );
				dp.setDeliverySN( rs.getInt("deliverySN") );
				dp.setDepFN( wid.stringFilter( rs.getString("depFN") ));
				dp.setDepmainSN( rs.getInt("depmainSN") );
				dp.setDepResult( rs.getString("depResult") );
				dp.seteDT( wid.changeTimeFormatUI( rs.getTimestamp("eDT") ) );
				dp.setEmpName( rs.getString("empName") );
				dp.setFileSizeInByte( rs.getInt("fileSizeInByte") );
				dp.setHostIP( rs.getString("hostIP") );
				dp.setLocalVer( rs.getInt("localVer") );
				
				ps.setInt(1, rs.getInt("depmainSN"));
				rs2 = ps.executeQuery();
				rs2.next();
				dp.setRowspan( rs2.getInt(1) );
				
				dps.add(dp);
			}
			
		} catch (Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) { rs.close(); }
				if ( rs2 !=null ) { rs2.close(); }
				if ( ps !=null ) { ps.close(); }
				if ( conn !=null ) { conn.close(); }
			} catch (Exception e) { e.printStackTrace(); }
		}
		
		response.getWriter().write(new Gson().toJson( dps ));
	}

}
